﻿using System;
using System.Collections.Generic;
using System.Text;

using MapCenterSystem.DataInfo;
using MapCenterSystem.DefineType;
using System.Data.SqlClient;
using MapCenterSystem.Configuration;
using System.Data;

namespace MapCenterSystem.DataAccess
{
    public class PlaceServiceEx : PlaceServices
    {
        public IList<PlaceInfo> GetPlaceByKeyword(String keyword)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceByKeyword";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.Parameters.AddWithValue("@keyword", keyword);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Picture = Convert.ToString(reader["Picture"]);
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Website = Convert.ToString(reader["Website"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    result.Add(place);
                }
                return result;
            }
        }

        public PlaceInfo GetPlaceById(int id)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceById";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", id);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                PlaceInfo place = new PlaceInfo();
                if (reader.Read())
                {
                    place.Address = Convert.ToString(reader["Address"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Picture = Convert.ToString(reader["Picture"]);
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Website = Convert.ToString(reader["Website"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                }
                return place;
            }
        }

        public IList<PlaceInfo> GetPlaceByPlaceType(int idType)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceByPlaceType";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@idType", idType);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.ID = Convert.ToInt64(reader["ID"]);
                    result.Add(place);
                }
                return result;
            }
        }


        public IList<PlaceInfo> GetPlaceByTypeAndDistance(int placeTypeID, Coordinate location, int limit)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceByPlaceTypeAndDistance";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@PlaceTypeID", placeTypeID);
                command.Parameters.AddWithValue("@xLocation", location.Latitude);
                command.Parameters.AddWithValue("@yLocation", location.Longitude);
                command.Parameters.AddWithValue("@Limit", limit);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Picture = Convert.ToString(reader["Picture"]);
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Website = Convert.ToString(reader["Website"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    result.Add(place);
                }
                return result;
            }
        }

        public IList<PlaceInfo> GetPlacebyTypeAround(int placeTypeID, Coordinate location, float distance)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_FindAllPlaceAround";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@LoaiDichVu", placeTypeID);
                command.Parameters.AddWithValue("@ViDo", location.Latitude);
                command.Parameters.AddWithValue("@KinhDo", location.Longitude);
                command.Parameters.AddWithValue("@KhoangCach", distance);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Picture = Convert.ToString(reader["Picture"]);
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Website = Convert.ToString(reader["Website"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    result.Add(place);
                }
                return result;
            }
        }
        public IList<PlaceInfo> GetPlaceByTypeAndAddress(int placeTypeID, string address)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceByTypeAndAddress";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@idType", placeTypeID);
                command.Parameters.AddWithValue("@address", address);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Picture = Convert.ToString(reader["Picture"]);
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Website = Convert.ToString(reader["Website"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    result.Add(place);
                }
                return result;
            }
        }

        public int AddPlace(PlaceInfo place)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_AddPlace";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@type", place.Type.ID);
                command.Parameters.AddWithValue("@fullName", place.FullName);
                command.Parameters.AddWithValue("@address", place.Address);
                command.Parameters.AddWithValue("@xLocation", place.Location.Latitude);
                command.Parameters.AddWithValue("@yLocation", place.Location.Longitude);
                command.Parameters.AddWithValue("@info", place.Info);
                command.Parameters.AddWithValue("@phone", place.Phone);
                command.Parameters.AddWithValue("@website", place.Website);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    return Convert.ToInt32(command.ExecuteScalar());
                }
                catch (SqlException ex)
                {
                    return -1;
                }
            }
        }

        public bool UpdatePlace(PlaceInfo place)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_UpdatePlace";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", place.ID);
                command.Parameters.AddWithValue("@type", place.Type.ID);
                command.Parameters.AddWithValue("@fullName", place.FullName);
                command.Parameters.AddWithValue("@address", place.Address);
                command.Parameters.AddWithValue("@xLocation", place.Location.Latitude);
                command.Parameters.AddWithValue("@yLocation", place.Location.Longitude);
                command.Parameters.AddWithValue("@info", place.Info);
                command.Parameters.AddWithValue("@phone", place.Phone);
                command.Parameters.AddWithValue("@website", place.Website);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    return false;
                }
                return true;
            }
        }

        public bool UpdatePlacePicture(int id, string picName)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_UpdatePicture";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", id);
                command.Parameters.AddWithValue("@picture", picName);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    return false;
                }
                return true;
            }
        }

        public bool DeletePlace(int id)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_DeletePlace";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", id);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    return false;
                }
                return true;
            }
        }
    }
}
